package com.ftg.learn.chapter10.jdbc;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.lang.reflect.*;
import java.sql.*;
import java.util.*;
import java.util.function.Supplier;

/**
 * T 为我们要操作的实体对像，即方法 成功以后返回的list<T></T>
 *
 * @author kangjx
 * @createTime 2022/7/19
 */
public class SimpleJdbcUtil<T> {

    private String username = "root";
    private String password = "Love0913@kjx";
    private String host = "127.0.0.1";
    private String port = "3306";
    private String databaseName = "cool2204";

    public SimpleJdbcUtil() {
    }

    /**
     * 可以根据传递的参数不同实现动态数据源
     *
     * @param host         ip
     * @param username     用户 名
     * @param password     密码
     * @param databaseName 数据实例名称
     */
    public SimpleJdbcUtil(String host, String username, String password, String databaseName) {
        this.host = host;
        this.username = username;
        this.password = password;
        this.databaseName = databaseName;
    }

    /**
     * 获得urlfbfh
     *
     * @return
     */
    private String getUrl() {
        return "jdbc:mysql://" + host + ":" + port + "/" + databaseName + "?serverTimezone=UTC";
    }

    /**
     * 获得数据库会话
     *
     * @return
     * @throws SQLException
     */
    public Connection myConnection() throws SQLException {
        return DriverManager.getConnection(getUrl(), this.username, this.password);
    }


    /**
     * 查询全部信息
     *json : js - java 轻量 [{"key":"value",。。。。}]
     * @param sql 语句
     * @param obj 参数
     * @return List<Map < String, Object>>
     */
    public List<Map<String, Object>> queryList(String sql, Object... obj) {
        List<Map<String, Object>> list = Collections.EMPTY_LIST;
        try (
            Connection conn = this.myConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql);
        ) {
            this.parameterForStatement(pstmt, obj);

            ResultSet rs = pstmt.executeQuery();
            if (Objects.nonNull(rs)) {
                list = new ArrayList<>(1);
            }
            return this.resultSetToMap(rs, list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * PreparedStatement里面的?赋值操作
     *
     * @param pstmt 预编译sql
     * @param obj   参数
     * @throws SQLException
     */
    private void parameterForStatement(PreparedStatement pstmt, Object... obj) throws SQLException {
        if (Objects.nonNull(obj)) {
            for (int i = 0, len = obj.length; i < len; i++) {
                pstmt.setObject(i + 1, obj[i]);
            }
        }
    }

    /**
     * 查询 全部信息
     *
     * @param sql 语句
     * @param obj 参数
     * @return List<T>
     */
    public List<T> queryPojo(String sql, Object... obj) {
        List<T> list = Collections.EMPTY_LIST;
        try (
                Connection conn = this.myConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ) {
            this.parameterForStatement(pstmt, obj);
            ResultSet rs = pstmt.executeQuery();
            if (Objects.nonNull(rs)) {
                list = new ArrayList<>(1);
            }
            return this.resultSetToPojo(rs, list);
        } catch (SQLException | ClassNotFoundException | InstantiationException | IllegalAccessException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 用来处理生成实体类的
     *
     * @param rs   结果集
     * @param list 返回值
     * @return List<T>
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    private List<T> resultSetToPojo(ResultSet rs, List<T> list) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        while (rs.next()) {

            Class c1 = this.getSuperParameterType();

            Object obj = c1.newInstance();

            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                String colName = resultSetMetaData.getColumnName(i + 1);
                try {
                    PropertyDescriptor pd = new PropertyDescriptor(colName, c1);
                    Method m1 = pd.getWriteMethod();
                    m1.invoke(obj, rs.getObject(i + 1));
                } catch (IntrospectionException | IllegalAccessException | InvocationTargetException | SQLException e) {
                    e.printStackTrace();
                }
            }
            list.add((T) obj);
        }
        return list;
    }

    /**
     * 获得参数化泛型
     *
     * @return
     * @throws ClassNotFoundException
     */
    private Class getSuperParameterType() throws ClassNotFoundException {
        ParameterizedType type = (ParameterizedType) this.getClass().getGenericSuperclass();
        Type[] ts = type.getActualTypeArguments();

        return Class.forName(ts[0].getTypeName());
    }

    /**
     * 通过rs转换成为list<Map></Map>
     *
     * @param rs   查询结果集
     * @param list 转换以后的数据
     * @return list<map></map>
     * @throws SQLException
     */
    private List<Map<String, Object>> resultSetToMap(ResultSet rs, List<Map<String, Object>> list) throws SQLException {

        ResultSetMetaData resultSetMetaData = this.getRsmd(rs);
        while (rs.next()) {
            Map<String, Object> map = new HashMap<>(4);
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                String columnName = resultSetMetaData.getColumnName(i + 1);
                Object value = rs.getObject(i + 1);
                map.put(columnName, value);
            }
            list.add(map);
        }
        return list;
    }

    private ResultSetMetaData getRsmd(ResultSet rs) throws SQLException {
        return rs.getMetaData();
    }

    /**
     * 添加 修改  删除
     *
     * @param sql sql
     * @param obj 参数
     * @return 行数
     */
    public int dml(String sql, Object... obj) {

        try (
                Connection conn = DriverManager.getConnection(getUrl(), this.username, this.password);
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ) {
            if (Objects.nonNull(obj)) {
                for (int i = 0, len = obj.length; i < len; i++) {
                    pstmt.setObject(i + 1, obj[i]);
                }
            }
            return pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }

    /**
     * 一般用来创建表，用户 ，视力等操作
     *
     * @param sql 语句
     * @param obj 参数
     * @return true|false
     */
    public boolean doOther(String sql, Object... obj) {

        try (
                Connection conn = DriverManager.getConnection(getUrl(), this.username, this.password);
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ) {
            if (Objects.nonNull(obj)) {
                for (int i = 0, len = obj.length; i < len; i++) {
                    pstmt.setObject(i + 1, obj[i]);
                }
            }
            return pstmt.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 生成全表添加的sql
     *
     * @return
     * @throws ClassNotFoundException
     */
    private String createInsertSql() throws ClassNotFoundException {
        Class c2 = this.getSuperParameterType();
        String tableName = c2.getSimpleName().toLowerCase(Locale.ROOT);
        StringBuilder stringBuilder = new StringBuilder("insert into ");
        stringBuilder.append(tableName)
                .append("(");
        Field[] fs = this.getClassFields(c2);
        String vals = "(";
        for (int i = 0; i < fs.length; i++) {
            stringBuilder.append(fs[i].getName()).append(i == fs.length - 1 ? "" : ",");
            vals += "?";
            vals += (i == fs.length - 1 ? "" : ",");
        }
        stringBuilder.append(")").append(" values ").append(vals).append(")");
        return stringBuilder.toString();
    }

    /**
     * 为添加方法 操作参数的
     * @param t1  参数
     * @return
     * @throws IntrospectionException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    private List<Object> deelTWithParam(T t1) throws IntrospectionException, InvocationTargetException, IllegalAccessException {
        List<Object> list = new ArrayList<>(5);
        Class c1 = t1.getClass();
        Field[] fs = this.getClassFields(c1);
        for (int i = 0; i < fs.length; i++) {
            PropertyDescriptor pd = new PropertyDescriptor(fs[i].getName(),c1);
            Method m = pd.getReadMethod();
            Object obj = m.invoke(t1);
            list.add(obj);
        }
        return list;
    }

    /**
     * 按照实体进行全表添加的操作
     * @param t1  参数
     * @return
     */
    public int save(T t1) {
        try {
            String sql = this.createInsertSql();
            List<Object> param = this.deelTWithParam(t1);
            return this.dml(sql, param.toArray());
        } catch (ClassNotFoundException | IntrospectionException | InvocationTargetException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return -1;
    }

    private Field[] getClassFields(Class c1) {
        Field[] fs = c1.getDeclaredFields();
        return fs;
    }

    /**
     * 按照传递过来的参数进行删除
     * @param where where条件
     * @param obj  参数
     * @return
     */
    public int deleteByParam(Supplier<String> where, Object...obj){
        Class c2 = null;
        try {
            c2 = this.getSuperParameterType();
            String tableName = c2.getSimpleName().toLowerCase(Locale.ROOT);
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("delete from ")
                    .append(tableName)
                    .append(" ")
                    .append(where.get());
            return this.dml(stringBuilder.toString(),obj);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return -1;
    }

    /**
     * 按照主键删除
     * @param id
     * @return
     */
    public int deleteById(Serializable id){
        String sql = "";
        try {
            Class c2 = this.getSuperParameterType();
            String tableName = c2.getSimpleName().toLowerCase(Locale.ROOT);
            Field[] fs = this.getClassFields(c2);
            for (int i = 0; i < fs.length; i++) {
                TableId tableIds = fs[i].getAnnotation(TableId.class);
                if(Objects.nonNull(tableIds)){
                    String key = !tableIds.value().equals("") ? tableIds.value() : fs[i].getName();
                    sql = "delete from " + tableName +" where " + key +" = ?";
                }
            }
            if(sql.equals("")){
                throw new RuntimeException("请为您的实体类添加@TablId用来声明主键");
            }
            return this.dml(sql,id);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return -1;
    }

    /**
     * 返回信息
     * @param sql
     * @param obj
     * @return
     */
    public String  queryPojoForJson(String sql,Object...obj){
        List<T> list = this.queryPojo(sql,obj);
        return JSON.toJSONString(list);
    }

    /**
     * 返回信息
     * @param sql
     * @param obj
     * @return
     */
    public String  queryMapForJson(String sql,Object...obj){
        List<Map<String,Object>> list = this.queryList(sql,obj);
        return JSON.toJSONString(list);
    }

    /**
     * 事务操作
     * @param sql  多条sql
     * @param objs  多组参数
     * @return
     */
    public boolean bathDml(String[] sql,Object[]...objs){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            conn = DriverManager.getConnection(getUrl(), this.username, this.password);
            conn.setAutoCommit(false);

            for (int i = 0; i < sql.length; i++) {
                pstmt = conn.prepareStatement(sql[i]);
                for (int i1 = 0; i1 < objs[i].length; i1++) {
                    pstmt.setObject(i1+1,objs[i][i1]);
                }
               pstmt.executeUpdate();
            }
            conn.commit();//手动提交
            return true;
        } catch (SQLException throwables) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            try {
                if (Objects.nonNull(conn)) {
                    conn.close();
                }
                if (Objects.nonNull(pstmt)) {
                    pstmt.close();
                }
            }catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return false;
    }

}
